class SpreadsheetFiller {

  constructor(options = {}) {
    let optiondef = {
      spreadsheet: null,
    },
    op = Object.assign({}, optiondef, options);

    if (!spreadsheet){
      throw new Error('spreadsheet is not empty!');
    }

    this.op = op;
  }

  //数值下标转EXCEL列序号
  convertColIndex(i) {
    let convert_tmp = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"];
    return convert_tmp[i];
  }

  createNewColumn(sheet, startColNum, allColumn, rowCount) {
    if (typeof startColNum !== 'number' || typeof allColumn !== 'number') {
      throw new Error('startColNum or allColumn must be number!');
    }
    if (allColumn === 0) { return }
    if (typeof rowCount !== 'number') {
      rowCount = sheet._rows._count > 200 ? 200 : sheet._rows._count;
    }

    let startColName = this.convertColIndex(startColNum - 1), 
        sourceRange = sheet.range(startColName + '1:' + startColName + rowCount), // A1:A50
        targetRange, 
        nextColName,
        curTmp = 1,
        r; // 已处理好样式内容的行数（首行为默认）

    for (r = 0; r < allColumn - 1; r++) { //插入空白记录
      sheet.insertColumn(startColNum);
    }

    while (curTmp < allColumn) {
      //逐行复制
      nextColName = this.convertColIndex(startColNum - 1 + curTmp);
      targetRange = sheet.range(nextColName + '1:' + nextColName + rowCount); // A1:A50; B1:B50; C1:C50
      this.op.spreadsheet._workbook.execute({
        command: 'AutoFillCommand', //复制插入行所在的上一行到插入行
        options: {
          operatingRange: targetRange, //目标
          origin: sourceRange //复制源
        }
      });

      // 设置目标的行高                     目标行高 = 复制源行高
      let sourceHeight = sheet.rowHeight(startColNum - 1);
      if (sourceHeight) {
        sheet.rowHeight((startColNum + curTmp - 1), sourceHeight);
      }

      curTmp++;
    }
  }

  createNewRow(sheet, startRowNum, allRows, columnCout) {
    if (typeof startRowNum !== 'number' || typeof allRows !== 'number') {
      throw new Error('startRowNum or allRows must be number!');
    }
    if (allRows === 0) { return }

    if (typeof columnCout !== 'number') {
      columnCout = sheet._columns._count > 50 ? 50 : sheet._columns._count;
    }

    let targetRange, sourceRange, maxColName = this.convertColIndex(columnCout - 1);
    for (let r = 0; r < allRows - 1; r++) { //插入空白记录
      sheet.insertRow(startRowNum + r);
    }

    let curTmp = 1; //已处理好样式内容的行数（首行为默认）
    while (curTmp < allRows) {
      //逐行复制
      sourceRange = sheet.range("A" + (startRowNum) + ":" + maxColName + (startRowNum));
      targetRange = sheet.range("A" + (startRowNum + curTmp) + ":" + maxColName + (startRowNum + curTmp)); // A1:E1; A2:E2; A3:E3
      this.op.spreadsheet._workbook.execute({
        command: 'AutoFillCommand', //复制插入行所在的上一行到插入行
        options: {
          operatingRange: targetRange, //目标
          origin: sourceRange //复制源
        }
      });

      // 设置目标的行高                     目标行高 = 复制源行高
      let sourceHeight = sheet.rowHeight(startRowNum - 1);
      if (sourceHeight) {
        sheet.rowHeight((startRowNum + curTmp - 1), sourceHeight);
      }

      curTmp++;
    }
    // if (allRows > 1) {
    //   sheet.range("A" + (startRowNum + 1) + ":" + maxColName + (startRowNum + allRows - 1))
    //     .tmsRule(null).tmsFillFlag(null).tmsCellFlag(null); //清除复制行的公式
    // }
  }

  doFill(rule, fillData) {
    let 
    sheet = this.op.spreadsheet.sheetByName(rule.sheetName), 
    startRowNum = rule.range.row, 
    startColNum = rule.range.col,
    rowCount = rule.range.rowCount,
    columnCount = rule.range.columnCout,
    that = this,
    tmsCellFlag,
    key;

    sheet.batch(() => {
      let startColName, lastRowNum, lastColNum, lastColName, range;
      switch(rule.type){
        case 'multi-column': // 填充列
          startColName = this.convertColIndex(startColNum - 1);
          tmsCellFlag = sheet.range(startColName + startRowNum).tmsCellFlag();

          // 汇总多列填充
          if (tmsCellFlag.dimensionBlock && fillData && fillData[tmsCellFlag.dimensionBlock]) {
            // 构建出跟多列填充一样的数据结构
            let tmpArray = []; 
            for(let keyName in fillData[tmsCellFlag.dimensionBlock]) {
              tmpArray.push({ dimensionOption: keyName, ...fillData[tmsCellFlag.dimensionBlock][keyName] });
            }
            // [{ dimensionBlock: '炼油', dkJe: 200 }, { dimensionBlock: '炼气', dkJe: 300 }];
            // console.log('tmpArray', tmpArray);
            fillData = tmpArray;
          }

          if (fillData.length) {
            lastColName = this.convertColIndex(startColNum + fillData.length - 2);
            this.createNewColumn(sheet, startColNum, fillData.length, rowCount);
            range = startColName + startRowNum + ':' + lastColName + sheet._rows._count; // A10:C200

            sheet.range(range).forEachCell(function (row, column, cell) {
              let cellValueObj = fillData[column - startColNum + 1];
              if (cell.tmsCellFlag && cell.tmsCellFlag.getRepValRelCompany && cellValueObj) {
                // 此处row从0开始计数
                sheet.range(that.convertColIndex(column) + (row + 1)).value(cellValueObj[cell.tmsCellFlag.getRepValRelCompany]);
              }
            });
          }
          break
        case 'row'://填行
          this.createNewRow(sheet, startRowNum, fillData.length, columnCount);
          
          fillData.forEach((item, index) => {
            for(key in item){
              Reflect.has(rule.range.fillCol, key) && 
              sheet.range(this.convertColIndex(rule.range.fillCol[key]) + (rule.range.row + index)).value(item[key]);
            }
          });
          break
        case 'range'://填充连接矩阵，填充行的效率改善，但限制必须是连接的行列填充，不允许跳列
          startColName = this.convertColIndex(startColNum - 1),
          lastRowNum = startRowNum + fillData.length - 1, //本身也要填充所以减1
          lastColNum = rule.range.col + fillData[0].length - 1,
          lastColName = this.convertColIndex(lastColNum - 1),
          range = startColName + startRowNum + ':' + lastColName + lastRowNum;

          this.createNewRow(sheet, startRowNum, fillData.length);
          sheet.range(range).values(fillData);
          break
        default:
          throw new Error('rule is error!');
          break
      }
    });
    this.op.spreadsheet.refresh();
  }
};